from sqlalchemy.exc import SQLAlchemyError


class BaseMapper:
    def __init__(self, session_factory):
        self.session_factory = session_factory

    def select_list(self, model, model_query=None):
        """
        通用查询方法，根据参数从数据库获取对象列表并转换为字典列表。
        :param model: SQLAlchemy模型类。
        :param model_query: 类实例或字典，包含查询过滤条件。
        :return: 包含字典的列表。
        """
        print("BaseMapper select_list ")
        print(model_query)

        try:
            with self.session_factory() as session:
                query = session.query(model)

                # 如果 model_query 是类实例，转换为字典
                if model_query and hasattr(model_query, 'to_dict'):
                    model_query = model_query.to_dict()

                # 移除 None 值的过滤条件
                model_query = {
                    k: v for k, v in (model_query or {}).items() if v is not None
                }

                # 如果提供了过滤参数，则进行过滤查询
                if model_query:
                    query = query.filter_by(**model_query)

                results = query.all()

                # session.commit()
                # 将每个实体对象返回
                return results

        except SQLAlchemyError as e:
            print(f"Database error during select_list operation: {str(e)}")
            return []

    def select_count(self, model, model_query=None):
        """
        通用统计方法，根据参数从数据库获取对象的数量。
        :param model: SQLAlchemy模型类。
        :param model_query: 类实例或字典，包含查询过滤条件。
        :return: 满足条件的对象数量。
        """
        print("BaseMapper select_count ")
        print(model_query)

        try:
            with self.session_factory() as session:
                query = session.query(model)

                # 如果 model_query 是类实例，转换为字典
                if model_query and hasattr(model_query, 'to_dict'):
                    model_query = model_query.to_dict()

                # 移除 None 值的过滤条件
                model_query = {
                    k: v for k, v in (model_query or {}).items() if v is not None
                }

                # 如果提供了过滤参数，则进行过滤查询
                if model_query:
                    query = query.filter_by(**model_query)

                # 返回统计的数量
                return query.count()

        except SQLAlchemyError as e:
            print(f"Database error during select_count operation: {str(e)}")
            return 0

    def insert(self, model):
        """
        插入单个对象到数据库。
        :param model: SQLAlchemy模型实例，表示要插入的数据。
        :return: 插入操作是否成功（True/False）。
        """
        print("BaseMapper insert ")
        print(model)

        try:
            with self.session_factory() as session:
                session.add(model)  # 添加模型实例到会话
                session.commit()  # 提交事务
                return True
        except SQLAlchemyError as e:
            print(f"Database error during insert operation: {str(e)}")
            return False

    def insert_or_update(self, model):
        """
        插入或更新单个对象到数据库。
        如果数据库中存在具有相同主键的记录，则执行更新操作；
        如果不存在，则执行插入操作。

        :param model: SQLAlchemy模型实例，表示要插入或更新的数据。
        :return: 操作是否成功（True/False）。
        """
        print("BaseMapper insert_or_update ")
        print(model)

        try:
            with self.session_factory() as session:
                # 自动检测模型的主键
                primary_key_columns = [key.name for key in model.__class__.__table__.primary_key.columns]

                # 构建过滤条件，自动根据主键查找
                filter_conditions = {pk: getattr(model, pk) for pk in primary_key_columns}

                # 查询是否已存在记录
                existing_record = session.query(model.__class__).filter_by(**filter_conditions).first()

                if existing_record:
                    # 更新记录
                    for key in model.__table__.columns.keys():
                        if not None and not key.startswith("_sa_"):  # 跳过 SQLAlchemy 内部属性
                            setattr(existing_record, key, getattr(model, key))
                else:
                    # 插入记录
                    session.add(model)

                session.commit()

                return True

        except SQLAlchemyError as e:
            print(f"Database error during insert_or_update operation: {str(e)}")
            return False

    def insert_batch(self, model_list):
        """
        批量插入数据到数据库。
        :param model_list: 包含多个模型实例的列表。
        :return: 插入成功的数量。
        """
        print("BaseMapper insert_batch ")
        print(model_list)

        try:
            with self.session_factory() as session:
                session.bulk_save_objects(model_list)  # 使用 bulk_save_objects 进行批量插入
                session.commit()

                return len(model_list)

        except SQLAlchemyError as e:
            print(f"Database error during insert_batch operation: {str(e)}")
            return 0

    def insert_or_update_batch(self, model, model_list):
        """
        批量插入或更新数据到数据库。
        :param model: SQLAlchemy模型类。
        :param model_list: 包含多个要插入或更新的模型实例的列表。
        :return: 插入或更新成功的数量。
        """
        print("BaseMapper insert_or_update_batch ")
        print(model_list)

        try:
            with self.session_factory() as session:
                count = 0
                for obj in model_list:
                    primary_key_columns = [key.name for key in model.__table__.primary_key.columns]

                    # 构建过滤条件，自动根据主键列查找
                    filter_conditions = {pk: getattr(obj, pk) for pk in primary_key_columns}
                    if any(value is None for value in filter_conditions.values()):
                        print(f"Skipping object due to missing primary key: {filter_conditions}")
                        continue

                    existing_obj = session.query(model).filter_by(**filter_conditions).first()

                    if existing_obj:
                        # 如果存在，进行更新操作
                        for column in obj.__table__.columns.keys():
                            if column not in primary_key_columns:  # 排除主键列
                                value = getattr(obj, column)
                                if value is not None:  # 跳过 None 值
                                    setattr(existing_obj, column, value)
                    else:
                        # 如果不存在，添加新记录
                        session.add(obj)

                    count += 1

                session.commit()

                return count

        except SQLAlchemyError as e:
            print(f"Database error during insert_or_update_batch operation: {str(e)}")
            return 0

    def update_by_params(self, model, model_bean, model_query):
        """
        根据多个条件更新数据库中的记录。

        :param model: SQLAlchemy模型类。
        :param model_bean: 类实例或字典，包含需要更新的字段及其新值。
        :param model_query: 类实例或字典，包含用于定位记录的过滤条件。
        :return: 操作是否成功（True/False）。
        """
        print("BaseMapper update_by_params ")
        print(model_bean)
        print(model_query)

        try:
            with self.session_factory() as session:
                # 如果 model_bean 是类实例，转换为字典
                if model_bean and hasattr(model_bean, 'to_dict'):
                    model_bean = model_bean.to_dict()

                # 如果 model_query 是类实例，转换为字典
                if model_query and hasattr(model_query, 'to_dict'):
                    model_query = model_query.to_dict()

                # 移除 None 值的过滤条件
                model_query = {
                    k: v for k, v in (model_query or {}).items() if v is not None
                }

                # 根据 model_query 定位记录
                query = session.query(model).filter_by(**model_query)

                # 过滤掉 None 值
                model_bean = {
                    key: value for key, value in model_bean.items() if value is not None
                }

                # 更新指定字段的值
                if model_bean:
                    query.update(model_bean)

                session.commit()

                return True

        except SQLAlchemyError as e:
            print(f"Database error during update_by_params operation: {str(e)}")
            return False

    def delete_by_param(self, model, model_query):
        """
        根据多个条件删除数据库中的记录。

        :param model: SQLAlchemy模型类。
        :param model_query: 类实例或字典，包含用于定位记录的过滤条件。
        :return: 操作是否成功（True/False）。
        """
        print("BaseMapper delete_by_param ")
        print(model_query)

        try:
            with self.session_factory() as session:
                # 如果 model_query 是类实例，转换为字典
                if model_query and hasattr(model_query, 'to_dict'):
                    model_query = model_query.to_dict()

                # 移除 None 值的过滤条件
                model_query = {
                    k: v for k, v in (model_query or {}).items() if v is not None
                }

                # 根据 filter_params 定位并删除记录
                query = session.query(model).filter_by(**model_query)
                num_rows_deleted = query.delete(synchronize_session=False)

                session.commit()

                print(f"{num_rows_deleted} rows were deleted.")
                return True

        except SQLAlchemyError as e:
            print(f"Database error during delete_by_param operation: {str(e)}")
            return False
